package com.hbwxz.admin.repo;

import com.hbwxz.admin.pojo.CommoditySpu;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface CommoditySpuRepository extends JpaRepository<CommoditySpu, Long> {
    @Query(value = "\n" +
            "SELECT  \n" +
            "\t\t\"TO DO\" AS image,\n" +
            "\t\tspuinfo.*,\n" +
            "    sku.id AS skuId,\n" +
            "    sku.price AS price,\n" +
            "    sku.stock AS stock,\n" +
            "    sku.title AS skuTitle,\n" +
            "    sku.own_attr AS ownAttr\n" +
            "FROM \n" +
            "    commodity_sku sku\n" +
            "LEFT JOIN (\n" +
            "    SELECT \n" +
            "        spu.id AS spuId,\n" +
            "        spu.brand_id AS brandId,\n" +
            "        spu.category_id AS categoryId,\n" +
            "        spudetail.generic_attr AS genericAttrs,\n" +
            "        spudetail.packing_list AS packingList,\n" +
            "        spudetail.customer_service AS customerService,\n" +
            "        brand.`name` AS brandName,\n" +
            "        category.`name` AS categoryName\n" +
            "    FROM \n" +
            "        commodity_spu spu\n" +
            "    JOIN commodity_spu_detail spudetail ON spu.id = spudetail.spu_id\n" +
            "    JOIN commodity_brand brand ON spu.brand_id = brand.id\n" +
            "    JOIN commodity_category category ON spu.category_id = category.id\n" +
            "    WHERE \n" +
            "        spu.id = :spuId\n" +
            ") AS spuinfo ON spuinfo.spuId = sku.spu_id;",nativeQuery = true)
    List<Map<String,Object>> getCommodityBySpuId(Long spuId);
}
